package wfc.util;

import wfc.entity.Cook;
import wfc.entity.Dish;
import wfc.entity.User;

import java.sql.*;

public class BaseDao {
    //数据库连接需要用到的一些对象
    static Connection connection = null;
    static PreparedStatement preparedStatement = null;
    static ResultSet resultSet = null;

    //读取外部数据库的配置信息
    static  final String PATH = "E:\\webWork\\Foods\\src\\resources\\database.properties";
    static  final  String DRIVER = ConfigManage.getConfig(PATH).getValue("driver");
    static  final  String URL = ConfigManage.getConfig(PATH).getValue("url");
    static  final  String USER = ConfigManage.getConfig(PATH).getValue("user");
    static  final  String PASS = ConfigManage.getConfig(PATH).getValue("password");

    //通用 增 删 改  返回操作成功行数
    public static int update(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                for (int i = 0; i < obj.length; i++) {
                    preparedStatement.setObject(i + 1, obj[i]);
                }
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //通用查询
    public static ResultSet executeSQL(String sql, Object[] params){
        connection = BaseDao.getConnection();
            try {
                preparedStatement= connection.prepareStatement(sql);
                if(params!=null){
                    for (int i=0;i<params.length;i++){
                        preparedStatement.setObject(i+1,params[i]);
                    }
                    resultSet = preparedStatement.executeQuery();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        return resultSet;
    }

    //javaBean实现分页
    public ResultSet selectByPage(String sql,Object[] obj){
        connection = BaseDao.getConnection();
            try {
                preparedStatement = connection.prepareStatement(sql);
                Page page = null;
                page = (Page) obj[0];
                preparedStatement.setObject(1,page.getIndex());
                preparedStatement.setObject(2,page.getLimit());
                resultSet= preparedStatement.executeQuery();
            }catch (SQLException e){
                e.printStackTrace();
            }
        return resultSet;
    }

    //User表
    //新增用户
    public static int userInsert(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection =BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            User news = (User) obj[0];
            if (obj != null && obj.length>0) {
                preparedStatement.setObject(1, news.getUsername());
                preparedStatement.setObject(2, news.getPassword());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
           BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //删 返回操作成功行数
    public static int userDelete(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                User news = (User) obj[0];
                //news_category表
                preparedStatement.setObject( 1,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
           BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //    改 返回操作成功行数
    public static int userUpdate(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection =BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                User news = (User) obj[0];
                //User表
                preparedStatement.setObject(1, news.getUsername());
                preparedStatement.setObject(2, news.getPassword());
                preparedStatement.setObject(3, news.getPhone());
                preparedStatement.setObject(4, news.getAvatar());
                preparedStatement.setObject( 5,news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
//    根据id查询所有信息  返回结果集
    public static ResultSet userSelectById(String sql, Object[] obj) {
        try {
            connection = BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                User user = (User) obj[0];
                preparedStatement.setObject(1,user.getId());
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    //根据用户名查用户信息
    public static ResultSet userSelectByName(String sql, Object[] obj) {
        try {
            connection = BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null && obj.length>0) {
                for (int i = 0; i<obj.length; ++i){
                    preparedStatement.setObject( i+1,obj[i]);
                }
                resultSet = preparedStatement.executeQuery();
            } else {
                resultSet = preparedStatement.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    //Dish表
    //新增菜单
    public static int dishInsert(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection =BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            Dish news = (Dish) obj[0];
            if (obj != null && obj.length>0) {
                preparedStatement.setObject(1, news.getDname());
                preparedStatement.setObject(2, news.getUname());
                preparedStatement.setObject(3, news.getLove());
                preparedStatement.setObject(4, news.getImg());
                preparedStatement.setObject(5, news.getCategory());

                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }
    //根据id修改菜品的信息
    public static int dishUpdate(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection = BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                Dish news = (Dish) obj[0];
                //news_category表
                preparedStatement.setObject( 3,news.getLove());
                preparedStatement.setObject( 2,news.getUname());
                preparedStatement.setObject(1, news.getDname());
                preparedStatement.setObject(4, news.getImg());
                preparedStatement.setObject(5, news.getCategory());
                preparedStatement.setObject(6, news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }


    //根据id修改菜谱的信息
    public static int cookUpdate(String sql, Object[] obj) {
        int rows = 0;
        try {
            connection =BaseDao.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (obj != null) {
                Cook news = (Cook) obj[0];
                preparedStatement.setObject( 3,news.getFufood());
                preparedStatement.setObject( 2,news.getMainfood());
                preparedStatement.setObject(1, news.getDname());
                preparedStatement.setObject(4, news.getTiaofood());
                preparedStatement.setObject(5, news.getProcess());
                preparedStatement.setObject(6, news.getCooking());
                preparedStatement.setObject(7, news.getCooktext());
                preparedStatement.setObject(8, news.getLove());
                preparedStatement.setObject(9, news.getId());
                rows = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            BaseDao.closeConnection(resultSet, preparedStatement, connection);
        }
        return rows;
    }



    //连接数据库驱动 返回驱动管理的对象
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USER, PASS);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭连接数据库所需要的所有对象
    public static void closeConnection(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
